package com.yhp.dao.impl;

import com.yhp.bean.Student;
import com.yhp.dao.GradeDao;
import com.yhp.dao.StudentDao;
import com.yhp.util.DBUtils01;
import com.yhp.util.Dbutils;
import com.yhp.util.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils01 implements StudentDao {

    @Override
    public int delete(String id) {
        int update = 0;
        try {
            String sql = "update student set state=? where stuno=?";
            List list = new ArrayList();
            list.add(StudentEnum.GRADUATE.id);
            list.add(id);
            update = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return update;
    }

    @Override
    public Student getById(String id) {
        Student student = null;
        System.out.println(id);
        try {
            String sql = "SELECT * FROM student where stuno=?";
            List list = new ArrayList();
            list.add(id);
            ResultSet query = query(sql,list);
            while (query.next()) {
                student = new Student();
                student.setStuName(query.getString("stuname"));
                student.setStuNo(query.getString("stuno"));
                student.setSex(query.getInt("sex"));
                student.setPhone(query.getString("phone"));
                student.setEmail(query.getString("email"));
                student.setRegistered(query.getString("registered"));
                student.setAddRess(query.getString("address"));
                student.setProfession(query.getString("profession"));
                student.setIdNumber(query.getString("idnumber"));
                student.setPolitics(query.getString("politics"));
                student.setRegDate(query.getDate("regdate"));
                student.setState(query.getInt("state"));
                student.setIntroduction(query.getString("introduction"));
                student.setGid(query.getInt("gid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int upDate(String id, Student student) {
        int update = 0;
        try {
            String sql = "update student set " +
                    "stuname=?,stuno=?,sex=?," +
                    "phone=?,email=?,registered=?," +
                    "address=?,profession=?,idnumber=?," +
                    "politics=?,introduction=?,gid=? " +
                    "where stuno=?";
            List list = new ArrayList();
            list.add(student.getStuName());
            list.add(student.getStuNo());
            list.add(student.getSex());
            list.add(student.getPhone());
            list.add(student.getEmail());
            list.add(student.getRegistered());
            list.add(student.getAddRess());
            list.add(student.getProfession());
            list.add(student.getIdNumber());
            list.add(student.getPolitics());
            list.add(student.getIntroduction());
            list.add(student.getGid());
            list.add(id);
            update = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }


    @Override
    public int addStudent(Student student) {

        int update = 0;
        try {
            String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List list = new ArrayList();
            list.add(student.getStuName());
            list.add(student.getStuNo());
            list.add(student.getSex());
            list.add(student.getPhone());
            list.add(student.getEmail());
            list.add(student.getRegistered());
            list.add(student.getAddRess());
            list.add(student.getProfession());
            list.add(student.getIdNumber());
            list.add(student.getPolitics());
            list.add(new Date());
            list.add(StudentEnum.READING.id);
            list.add(student.getIntroduction());
            list.add(student.getGid());
            update = update(sql,list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return update;
    }



    @Override
    public int total(String name, String id, int sex) {
        int total = 0;
        ArrayList list1 = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer(" SELECT count(*) FROM student where 1=1 and state!=4 ");
            if (name != null && name.length() > 0) {
                sql.append("and stuname like ? ");
                list1.add("%" + name + "%");
            }
            if (id != null && id.length() > 0) {
                sql.append("and stuno = ? ");
                list1.add(id);
            }
            if (sex != -1) {
                sql.append("and sex = ? ");
                list1.add(sex);

            }
            ResultSet query = query(sql.toString(), list1);
            while (query.next()){
                total = query.getInt(1);
            }



        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return total;
    }

    @Override
    public List<Student> getStudent(String name, String id, int sex,int pageIndex,int pageSize) {
        ArrayList<Student> list = new ArrayList();
        ArrayList list1 = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer(" SELECT * FROM student where 1=1 and state!=4 ");
            if(name != null && name.length()>0){
                sql.append("and stuname like ? ");
                list1.add("%"+name+"%");
            }
            if(id != null && id.length()>0){
                sql.append("and stuno = ? ");
                list1.add(id);
            }
            if(sex != -1){
                sql.append("and sex = ? ");
                list1.add(sex);

            }

            //分页
            sql.append(" limit ?,? ");
            //limit (pageIndex-1)*pageSize,pageSize;
            list1.add((pageIndex-1)*pageSize);
            list1.add(pageSize);




            ResultSet query = query(sql.toString(), list1);

            while (query.next()){
                Student student = new Student();
                student.setStuId(query.getInt("stuid"));
                student.setStuName(query.getString("stuname"));
                student.setStuNo(query.getString("stuno"));
                student.setSex(query.getInt("sex"));
                student.setPhone(query.getString("phone"));
                student.setEmail(query.getString("email"));
                student.setRegistered(query.getString("registered"));
                student.setAddRess(query.getString("address"));
                student.setProfession(query.getString("profession"));
                student.setIdNumber(query.getString("idnumber"));
                student.setPolitics(query.getString("politics"));
                student.setRegDate(query.getDate("regdate"));
                student.setState(query.getInt("state"));
                student.setIntroduction(query.getString("introduction"));
                student.setGid(query.getInt("gid"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;


    }
}
